﻿/*blog_Settings*/
IF OBJECT_ID(N'[dbo].[blog_Settings]') IS NULL
BEGIN
    CREATE TABLE [dbo].[blog_Settings] (
		[set_name] NVARCHAR(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[set_type] TINYINT NOT NULL ,
		[set_value0] INT NOT NULL ,
		[set_value1] TEXT NOT NULL 
    ) ON [PRIMARY]
END
GO
/*blog_Category*/
IF OBJECT_ID(N'[dbo].[blog_Category]') IS NULL
BEGIN
    CREATE TABLE [dbo].[blog_Category] (
		[cat_id] INT IDENTITY (1, 1) NOT NULL ,
		[cat_name] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[cat_order] INT NOT NULL ,
		[cat_articleCount] INT NOT NULL ,
		[cat_hidden] BIT NOT NULL ,
		[cat_locked] BIT NOT NULL 
    ) ON [PRIMARY]
END
GO
/*blog_Article*/
IF OBJECT_ID(N'[dbo].[blog_Article]') IS NULL
BEGIN
    CREATE TABLE [dbo].[blog_Article] (
		[log_id] INT IDENTITY (1, 1) NOT NULL ,
		[log_catID] INT NOT NULL ,
		[log_title] NVARCHAR(255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[log_authorID] INT NOT NULL ,
		[log_author] NVARCHAR(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[log_editMark] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[log_trackbackURL] NVARCHAR(255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[log_content0] TEXT NOT NULL ,
		[log_content1] TEXT NOT NULL ,
		[log_mode] INT NOT NULL ,
		[log_locked] BIT NOT NULL ,
		[log_selected] BIT NOT NULL ,
		[log_ubbFlags] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NULL ,
		[log_postTime] DATETIME NOT NULL ,
		[log_ip] NVARCHAR(15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[log_commentCount] INT NOT NULL ,
		[log_viewCount] INT NOT NULL ,
		[log_trackbackCount] INT NOT NULL ,
		[log_IsTop] BIT NULL ,
		[log_EnableRss] BIT NULL 
    ) ON [PRIMARY]
END
GO
/*blog_Comment*/
IF OBJECT_ID(N'[dbo].[blog_Comment]') IS NULL
BEGIN
    CREATE TABLE [dbo].[blog_Comment] (
		[comm_id] INT IDENTITY (1, 1) NOT NULL ,
		[log_id] INT NOT NULL ,
		[comm_content] TEXT NOT NULL ,
		[comm_authorID] INT NOT NULL ,
		[comm_author] NVARCHAR(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[comm_editMark] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NULL ,
		[comm_hidden] BIT NOT NULL ,
		[comm_ubbFlags] NVARCHAR(10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[comm_postTime] DATETIME NOT NULL ,
		[comm_ip] NVARCHAR(15) COLLATE Chinese_PRC_CI_AS NOT NULL 
    ) ON [PRIMARY]
END
GO
/*blog_Trackback*/
IF OBJECT_ID(N'[dbo].[blog_Trackback]') IS NULL
BEGIN
    CREATE TABLE [dbo].[blog_Trackback] (
		[tb_id] INT IDENTITY (1, 1) NOT NULL ,
		[log_id] INT NULL ,
		[tb_url] NVARCHAR(100) COLLATE Chinese_PRC_CI_AS NULL ,
		[tb_title] NVARCHAR(100) COLLATE Chinese_PRC_CI_AS NULL ,
		[tb_blog] NVARCHAR(100) COLLATE Chinese_PRC_CI_AS NULL ,
		[tb_excerpt] NVARCHAR(500) COLLATE Chinese_PRC_CI_AS NULL ,
		[tb_time] DATETIME NULL ,
		[tb_ip] NVARCHAR(15) COLLATE Chinese_PRC_CI_AS NULL 
    ) ON [PRIMARY]
END
GO
/*blog_UserGroup*/
IF OBJECT_ID(N'[dbo].[blog_UserGroup]') IS NULL
BEGIN
    CREATE TABLE [dbo].[blog_UserGroup] (
		[group_id] INT IDENTITY (1, 1) NOT NULL ,
		[group_name] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[group_rights] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL 
    ) ON [PRIMARY]
END
GO
/*blog_User*/
IF OBJECT_ID(N'[dbo].[blog_User]') IS NULL
BEGIN
    CREATE TABLE [dbo].[blog_User] (
		[user_id] INT IDENTITY (1, 1) NOT NULL ,
		[user_name] NVARCHAR(25) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[user_password] NVARCHAR(40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[user_salt] NVARCHAR(6) COLLATE Chinese_PRC_CI_AS NULL ,
		[user_groupID] INT NULL ,
		[user_gender] TINYINT NULL ,
		[user_email] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NULL ,
		[user_hideEmail] BIT NOT NULL ,
		[user_homepage] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NULL ,
		[user_articleCount] INT NULL ,
		[user_commentCount] INT NULL ,
		[user_lastVisit] DATETIME NULL ,
		[user_ip] NVARCHAR(15) COLLATE Chinese_PRC_CI_AS NULL ,
		[user_hashKey] NVARCHAR(40) COLLATE Chinese_PRC_CI_AS NULL 
    ) ON [PRIMARY]
END
GO
/*Guestbook*/
IF OBJECT_ID(N'[dbo].[Guestbook]') IS NULL
BEGIN
    CREATE TABLE [dbo].[Guestbook] (
		[gb_id] INT IDENTITY (1, 1) NOT NULL ,
		[gb_username] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[gb_userID] INT NOT NULL ,
		[gb_content] TEXT NOT NULL ,
		[gb_editMark] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[gb_ubbFlags] NVARCHAR(10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[gb_postTime] DATETIME NOT NULL ,
		[gb_replyUsername] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NULL ,
		[gb_reply] TEXT NOT NULL ,
		[gb_replyTime] DATETIME NOT NULL ,
		[gb_hidden] BIT NOT NULL ,
		[gb_ip] NVARCHAR(15) COLLATE Chinese_PRC_CI_AS NOT NULL 
    ) ON [PRIMARY]
END
GO
/*blog_Smile*/
IF OBJECT_ID(N'[dbo].[blog_Smilies]') IS NULL
BEGIN
    CREATE TABLE [dbo].[blog_Smilies] (
		[sm_id] INT IDENTITY (1, 1) NOT NULL ,
		[sm_image] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NULL ,
		[sm_code] NVARCHAR(25) COLLATE Chinese_PRC_CI_AS NULL 
    ) ON [PRIMARY]
END
GO
/*blog_WordFilter*/
IF OBJECT_ID(N'[dbo].[blog_WordFilter]') IS NULL
BEGIN
    CREATE TABLE [dbo].[blog_WordFilter] (
		[wf_id] INT IDENTITY (1, 1) NOT NULL ,
		[wf_mode] TINYINT NOT NULL ,
		[wf_text] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[wf_replace] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[wf_regExp] BIT NOT NULL 
    ) ON [PRIMARY]
END
GO
/*blog_VisitorRecord*/
IF OBJECT_ID(N'[dbo].[blog_VisitorRecord]') IS NULL
BEGIN
    CREATE TABLE [dbo].[blog_VisitorRecord] (
		[vr_id] INT IDENTITY (1, 1) NOT NULL ,
		[vr_ip] NVARCHAR(15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[vr_os] NVARCHAR(20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[vr_browser] NVARCHAR(30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[vr_time] DATETIME NOT NULL ,
		[vr_referer] NVARCHAR(250) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[vr_target] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL 
    ) ON [PRIMARY]
END
GO

/*blog_ExtensionLink*/
IF OBJECT_ID(N'[dbo].[blog_ExtensionLink]') IS NULL
BEGIN
    CREATE TABLE [dbo].[blog_ExtensionLink] (
		[linkID] INT IDENTITY (1, 1) NOT NULL ,
		[Name] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[Url] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[Extensions] NVARCHAR(100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[Type] INT NOT NULL ,
		[orderID] INT NOT NULL 
    ) ON [PRIMARY]
END

/*blog_Widget*/
IF OBJECT_ID(N'[dbo].[blog_Widget]') IS NULL
BEGIN
    CREATE TABLE [dbo].[blog_Widget] (
		[widgetID] INT IDENTITY (1, 1) NOT NULL ,
		[widget_Name] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[widget_Title] NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
		[widget_Hidden] BIT NOT NULL ,
		[widget_Type] INT NOT NULL ,
		[widget_Order] INT NOT NULL ,
		[widget_Rate] INT NOT NULL ,
		[widget_InstallTime] SMALLDATETIME NOT NULL ,
		[widget_IsInstall] BIT NOT NULL ,
		[widget_Content] TEXT NOT NULL ,
		[widget_IsSystem] BIT NOT NULL 
    ) ON [PRIMARY]
END

/*blog_Tag*/
IF OBJECT_ID(N'[dbo].[blog_Tag]') IS NULL
BEGIN
    CREATE TABLE [dbo].[blog_Tag] (
		[tag_ID] INT IDENTITY (1, 1) NOT NULL ,
		[log_ID] INT NOT NULL ,
		[tag_Name] VARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL 
    ) ON [PRIMARY]
END


GO

--region Create indexes
IF OBJECT_ID('PK_blog_Settings') IS NULL
BEGIN
	ALTER TABLE [dbo].[blog_Settings] ADD CONSTRAINT [PK_blog_Settings] PRIMARY KEY CLUSTERED
	(
		[set_name]
	)
	ON [PRIMARY]
END

IF OBJECT_ID('PK_blog_Category') IS NULL
BEGIN
	ALTER TABLE [dbo].[blog_Category] ADD CONSTRAINT [PK_blog_Category] PRIMARY KEY CLUSTERED
	(
		[cat_id]
	)
	ON [PRIMARY]
END

IF OBJECT_ID('PK_blog_Article') IS NULL
BEGIN
	ALTER TABLE [dbo].[blog_Article] ADD CONSTRAINT [PK_blog_Article] PRIMARY KEY CLUSTERED
	(
		[log_id]
	)
	ON [PRIMARY]
END

IF OBJECT_ID('PK_blog_Comment') IS NULL
BEGIN
	ALTER TABLE [dbo].[blog_Comment] ADD CONSTRAINT [PK_blog_Comment] PRIMARY KEY CLUSTERED
	(
		[comm_id]
	)
	ON [PRIMARY]
END

IF OBJECT_ID('PK_blog_Trackback') IS NULL
BEGIN
	ALTER TABLE [dbo].[blog_Trackback] ADD CONSTRAINT [PK_blog_Trackback] PRIMARY KEY CLUSTERED
	(
		[tb_id]
	)
	ON [PRIMARY]
END

IF OBJECT_ID('PK_blog_UserGroup') IS NULL
BEGIN
	ALTER TABLE [dbo].[blog_UserGroup] ADD CONSTRAINT [PK_blog_UserGroup] PRIMARY KEY CLUSTERED
	(
		[group_id]
	)
	ON [PRIMARY]
END

IF OBJECT_ID('PK_blog_User') IS NULL
BEGIN
	ALTER TABLE [dbo].[blog_User] ADD CONSTRAINT [PK_blog_User] PRIMARY KEY CLUSTERED
	(
		[user_id]
	)
	ON [PRIMARY]
END

IF OBJECT_ID('PK_Guestbook') IS NULL
BEGIN
	ALTER TABLE [dbo].[Guestbook] ADD CONSTRAINT [PK_Guestbook] PRIMARY KEY CLUSTERED
	(
		[gb_id]
	)
	ON [PRIMARY]
END

IF OBJECT_ID('PK_blog_Smilies') IS NULL
BEGIN
	ALTER TABLE [dbo].[blog_Smilies] ADD CONSTRAINT [PK_blog_Smilies] PRIMARY KEY CLUSTERED
	(
		[sm_id]
	)
	ON [PRIMARY]
END

IF OBJECT_ID('PK_blog_WordFilter') IS NULL
BEGIN
	ALTER TABLE [dbo].[blog_WordFilter] ADD CONSTRAINT [PK_blog_WordFilter] PRIMARY KEY CLUSTERED
	(
		[wf_id]
	)
	ON [PRIMARY]
END


IF OBJECT_ID('PK_blog_VisitorRecord') IS NULL
BEGIN
	ALTER TABLE [dbo].[blog_VisitorRecord] ADD CONSTRAINT [PK_blog_VisitorRecord] PRIMARY KEY CLUSTERED
	(
		[vr_id]
	)
	ON [PRIMARY]
END

IF OBJECT_ID('PK_blog_ExtensionLink') IS NULL
BEGIN
	ALTER TABLE [dbo].[blog_ExtensionLink] ADD CONSTRAINT [PK_blog_ExtensionLink] PRIMARY KEY CLUSTERED
	(
		[linkID]
	)
	ON [PRIMARY]
END

IF OBJECT_ID('PK_blog_Widget') IS NULL
BEGIN
	ALTER TABLE [dbo].[blog_Widget] ADD CONSTRAINT [PK_blog_Widget] PRIMARY KEY CLUSTERED
	(
		[widgetID]
	)
	ON [PRIMARY]
END

IF OBJECT_ID('PK_blog_Tag') IS NULL
BEGIN
	ALTER TABLE [dbo].[blog_Tag] ADD CONSTRAINT [PK_blog_Tag] PRIMARY KEY CLUSTERED
	(
		[tag_ID]
	)
	ON [PRIMARY]
END


--endregion